import cherrypy
import sqlite3
import time

import Common

# restructure the forum
class Structure(Common.Template):
	# Call the default function with no arguments
	def index(self):
		return self.default()
	index.exposed = True
	
	# This function is the default view for the restructure view and shows the 
	# subforum tree along with all of the commands for each subforum
	def default(self):
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		else:
			# display the current forum structure
			yield self.header("Restructure")
			yield self.links()
			yield self.breadcrumbs()
			
			# Print the tree
			yield self.Tree()
			
			yield self.footer()
	
	# This function is recursively called by default to build the subforum Tree.
	# It continualy builds nested unordered (bulleted) lists.
	def Tree(self, id = "NULL"):
		cursor = cherrypy.thread_data.db.cursor() 	
		cursor.execute("SELECT ID, Name FROM Subforum WHERE ParentSubforumID IS ? ORDER BY RelativeOrder", (id,))
		
		out = ""
		for ID, Name in cursor.fetchall():
			out += "<li>"
			
			out += Name + " (<a href='AddSub/%s'>Add Subforum</a> | <a href='Relocate/%s'>Relocate</a> | <a href='Delete/%s'>Delete</a> | <a href='EditMod/%s'>Edit Moderators</a> | <a href='Up/%s'>Up</a> | <a href='Down/%s'>Down</a> | <a href='Top/%s'>Top</a> | <a href='Bottom/%s'>Bottom</a>)" % (ID, ID, ID, ID, ID, ID, ID, ID)
			
			out += "</li>"
			
			out += self.Tree(ID)
		
		if out != "":
			out = "<ul>" + out + "</ul>"		
		
		if id == "NULL":
			out = "<ul><li><i>Forum Root</i> (<a href='AddSub/NULL'>Add Subforum</a> | <font style='color: gray'><u>Relocate</u></font> | <font style='color: gray'><u>Delete</u></font> | <a href='EditMod/NULL'>Edit Moderators</a> | <font style='color: gray'><u>Up</u></font> | <font style='color: gray'><u>Down</u></font> | <font style='color: gray'><u>Top</u></font> | <font style='color: gray'><u>Bottom</u></font>)</li>" + out + "</ul>"
		
		return out
	
	#[todo] Add more robust error-checking.
		def EditMod(self, ForumID, ModList = None):
				# figure out the subforum name
				name = self.get_subforum_name(ForumID)
				if name == None and ForumID == "NULL":
						name = "Forum Root"
				if self.getLoginPermission() != 0:
						# Not an admin, error
						yield self.error('You do not have permission to view this page!', True)
				elif name == "Forum Root" and ForumID != "NULL":
						# The thread is not found, so error
						yield self.error('Specified subforum not found.')
				elif ModList == None:
						# get list of mods for the subforum
						cursor = cherrypy.thread_data.db.cursor()
						cursor.execute('''
								SELECT
										User.Username
								FROM
										User, Moderator
								WHERE
										User.Permission = 1 AND
										User.ID = Moderator.UserID AND
										Moderator.SubforumID IS ?
						''', (ForumID,))
						mods = ""
						for mod in cursor.fetchall():
								mods += mod[0] + "\n"
						# display moderator entry form
						yield self.header("Edit Moderators")
						yield self.links()
						yield self.breadcrumbs()
						yield '''
								<form method="post" action="%s">
										<h2>Moderator List for "%s"</h2>
										Please enter one username per line:<br /><br />
										<textarea class="monospace" type="text" name="ModList" rows="8" cols="40">%s</textarea><br />
										<input type="submit" value="Update" />
								</form>
						''' % (ForumID, name, mods)
						yield self.footer()
				else:
						cursor = cherrypy.thread_data.db.cursor()
						# This PRAGMA is needed to ensure recursive triggers work
						cursor.execute("PRAGMA recursive_triggers = 1;")
						cherrypy.thread_data.db.commit()
						# get a list of the old mod's user ids
						cursor.execute('''
								SELECT
										User.ID
								FROM
										User, Moderator
								WHERE
										User.Permission = 1 AND
										User.ID = Moderator.UserID AND
										Moderator.SubforumID IS ?
						''', (ForumID,))
						oldmods = cursor.fetchall()
						# format old mod ids into a query
						if len(oldmods) > 0:
								old = ""
								for uid in oldmods:
										old += '''ID = %s OR ''' % uid
								old = old.rstrip(' OR')
								# remove all moderators for this subforum
								cursor.execute('''UPDATE User SET Permission = 2 WHERE %s''' % old)
								cherrypy.thread_data.db.commit()
						cursor.execute('''DELETE FROM Moderator WHERE SubforumID IS ?''', (ForumID,))
						cherrypy.thread_data.db.commit()
						# format the moderator list into a query
						mods = ""
						ModList = list(set([mod.rstrip() for mod in ModList.split("\n") if mod.strip() != '']))
						for mod in ModList:
								mods += '''Username LIKE "%s" OR ''' % mod
						mods = mods.rstrip(' OR')
						# get the new mod's user ids
						cursor.execute('''SELECT ID FROM User WHERE %s''' % mods)
						ModList = cursor.fetchall()
						# add the new list of moderators for this subforum
						cursor.execute('''UPDATE User SET Permission = 1 WHERE %s''' % mods)
						cherrypy.thread_data.db.commit()
						for uid in ModList:
								cursor.execute('''INSERT INTO Moderator VALUES (%s,%s)''' % (uid[0], ForumID))
						# commit changes
						cherrypy.thread_data.db.commit()
						# display success message
						yield self.redirect("/Structure")
		EditMod.exposed = True
	
	# This function is the add subforum form.  If the Name and Description args
	# are not given, then the form to create the subforum is showed.  If they
	# are set, then the given subforum is created benesath the Parent subforum
	def AddSub(self, ParentID, Name = None, Description = None):		
		cursor = cherrypy.thread_data.db.cursor() 
		
		# Determine if the given subforum has child threads
		cursor.execute("SELECT * FROM Thread WHERE Thread.SubforumID IS ?", (ParentID,))		
		hasthreads = cursor.fetchone() != None
		
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		elif self.get_subforum_name(ParentID) == None and ParentID != "NULL":
			# The thread is not found, so error
			yield self.error('Specified subforum not found.')
		elif Name == None or Name == "":
			# There is no name given, so show the create form
			yield self.header("Add Subforum")
			yield self.links()
			yield self.breadcrumbs()
			
			yield "<form method='post' action='%s'>" % ParentID
			yield "Subforum Name: <br />"
			yield "<input class='monospace' type='text' name='Name' size='60' /><br />"
			yield "Description: <br />"
			yield "<textarea class='monospace' name='Description' rows='3' cols='80'></textarea><br />"
			yield "<input type='submit' value='New Subforum' />"
			yield "</form>"
		
			yield self.footer()
		else:			
			# Get the largest relative order value in this subforum.  If there are
			# no subforums, set it to 0
			cursor.execute("SELECT MAX(Subforum.RelativeOrder) FROM Subforum WHERE Subforum.ParentSubforumID IS ?", (ParentID,))
			
			max = cursor.fetchone()[0]			
			if max == None:
				order = 0
			else:
				order = max + 1
			
			# Create the new subforum.  Recursive triggers are not needed in this case
			cursor.execute('''
				INSERT INTO Subforum (ID, Name, Description, ParentSubforumID, ThreadCount, PostCount, RelativeOrder)
				VALUES (NULL, ?, ?, ?, 0, 0, ?)
				''', (Name, Description, ParentID, order,))
			
			cherrypy.thread_data.db.commit()		
			
			# Return to the restructure page
			yield self.redirect("/Structure")	
	AddSub.exposed = True
	
	# This function moves a subforum underneath a new parent.  If NewParentID
	# is not set, it shows the form, otherwise, it moves the subforum
	def Relocate(self, id, NewParentID = None):					
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		elif self.get_subforum_name(id) == None:
			# The thread is not found, so error
			yield self.error('Specified subforum not found.')
		elif NewParentID == None or id == NewParentID:
			# The new parent is not valid, so show the form
			yield self.header("Relocate Subforum")
			yield self.links()
			yield self.breadcrumbs()
			
			yield "<p>"
			yield "<form method='post' action='%s'>" % id
		
			# Show the subforum tree
			yield self.RelocateTree()
			
			yield "<br /><br /><input type='submit' value='Move Subforum' />"
			yield "</form>"
			yield "</p>"
		
			yield self.footer()
		else:
			cursor = cherrypy.thread_data.db.cursor() 
			
			# This PRAGMA is needed to ensure recursive triggers work
			cursor.execute("PRAGMA recursive_triggers = 1;")
			cherrypy.thread_data.db.commit()
			
			# Movethe subforum to the new parent
			cursor.execute("UPDATE Subforum SET ParentSubforumID = ? WHERE ID = ?", (NewParentID, id,))
			
			cherrypy.thread_data.db.commit()
			
			# Move this subforum to the bottom of the subforum
			self.Bottom(id)				
			
			yield self.redirect("/Structure")	
	Relocate.exposed = True
	
	# This function creates the drop down tree structure of the subforum tree.
	# Depth is acutally a string of mdashes that is added to at each level
	def RelocateTree(self, id = "", depth = ""):		
		if id == "" and depth == "":
			# This is the root, so make the root node and then recursively call self
			out = "<select name='NewParentID'><option value='NULL' style='font-style: italic;'>(Forum Root)</option>" + self.RelocateTree("NULL", "&mdash;") + "</select>"	
		else:
			# Get all of the children of the given parent and then recursively call RelocateTree
			cursor = cherrypy.thread_data.db.cursor() 	
			cursor.execute("SELECT ID, Name FROM Subforum WHERE ParentSubforumID IS ?", (id,))
			
			out = ""
			for ID, Name in cursor.fetchall():
				out += "<option value='%s'>%s%s</option>" % (ID, depth, Name)			
				out += self.RelocateTree(ID, depth + "&mdash;")		
			
		return out
	
	# This deletes a given subforum and in turn all of its containing subforums,
	# threads, and posts via triggers
	def Delete(self, id):					
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		elif self.get_subforum_name(id) == None:
			# The thread is not found, so error
			yield self.error('Specified subforum not found.')
		else:
			cursor = cherrypy.thread_data.db.cursor() 
			
			# This PRAGMA is needed to ensure recursive triggers work
			cursor.execute("PRAGMA recursive_triggers = 1;")
			cherrypy.thread_data.db.commit()
			
			cursor.execute("DELETE FROM Subforum WHERE ID = ?", (id,))
			cherrypy.thread_data.db.commit()
			
			# Triggers will delete subforums, threads, posts and update post/thread counts
			
			yield self.redirect("/Structure")
	Delete.exposed = True
	
	# This moves a given subforum to the top of the ordering in the given 
	# subforum by setting its order to one less than the minimum in that 
	# subforum
	def Top(self, id):					
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		elif self.get_subforum_name(id) == None:
			# The thread is not found, so error
			yield self.error('Specified subforum not found.')
		else:
			cursor = cherrypy.thread_data.db.cursor() 
			
			# Get the minimum subforum in that subforum
			cursor.execute("SELECT MIN(Subforum.RelativeOrder) FROM Subforum WHERE Subforum.ParentSubforumID IS (SELECT Subforum.ParentSubforumID FROM Subforum WHERE Subforum.ID = ?)", (id,))			
			
			# Change the given subforum's order
			cursor.execute("UPDATE Subforum SET RelativeOrder = ? WHERE ID = ?", ((cursor.fetchone()[0] - 1), id,))
			
			cherrypy.thread_data.db.commit()
			
			yield self.redirect("/Structure")
	Top.exposed = True
	
	# This moves a given subforum to the bottom of the ordering in the given 
	# subforum by setting its order to one more than the maximum in that 
	# subforum
	def Bottom(self, id):					
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		elif self.get_subforum_name(id) == None:
			# The thread is not found, so error
			yield self.error('Specified subforum not found.')
		else:
			cursor = cherrypy.thread_data.db.cursor() 
			
			# Get the maximum subforum in that subforum
			cursor.execute("SELECT MAX(Subforum.RelativeOrder) FROM Subforum WHERE Subforum.ParentSubforumID IS (SELECT Subforum.ParentSubforumID FROM Subforum WHERE Subforum.ID = ?)", (id,))				
			
			# Change the given subforum's order	
			cursor.execute("UPDATE Subforum SET RelativeOrder = ? WHERE ID = ?", ((cursor.fetchone()[0] + 1), id,))
			
			cherrypy.thread_data.db.commit()
			
			yield self.redirect("/Structure")
	Bottom.exposed = True	
	
	# This function moves a given subforum down.  To do this, it swaps its order
	# with the subforum "below" it
	def Down(self, MoveDownID):
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		elif self.get_subforum_name(MoveDownID) == None:
			# The thread is not found, so error
			yield self.error('Specified subforum not found.')
		else:
			cursor = cherrypy.thread_data.db.cursor() 
			
			# Get this subforum's relative order
			cursor.execute("SELECT Subforum.RelativeOrder FROM Subforum WHERE Subforum.ID = ?", (MoveDownID,))			
			MoveDownOrder = cursor.fetchone()[0]
			
			# Get the ID and Relative order of the subforum below this
			cursor.execute('''
				SELECT Subforum.ID, Subforum.RelativeOrder
				FROM Subforum
				WHERE Subforum.RelativeOrder IS
					(
					SELECT MIN(Subforum.RelativeOrder)
					FROM Subforum
					WHERE 
					Subforum.ParentSubforumID IS
						(
						SELECT Subforum.ParentSubforumID
						FROM Subforum
						WHERE Subforum.ID = ?
						)
					AND
					Subforum.RelativeOrder >
						(
						SELECT Subforum.RelativeOrder
						FROM Subforum
						WHERE Subforum.ID = ?
						)
					)
					AND Subforum.ParentSubforumID IS
					(
					SELECT Subforum.ParentSubforumID
					FROM Subforum
					WHERE Subforum.ID = ?
					)
				''', (MoveDownID, MoveDownID, MoveDownID,))
			
			result = cursor.fetchone()
			
			if result != None:
				(MoveUpID, MoveUpOrder) = result
				
				# Swap the orders
				cursor.execute("UPDATE Subforum SET RelativeOrder = ? WHERE ID = ?", (MoveDownOrder,MoveUpID,))
				cherrypy.thread_data.db.commit()
				
				cursor.execute("UPDATE Subforum SET RelativeOrder = ? WHERE ID = ?", (MoveUpOrder,MoveDownID,))
				cherrypy.thread_data.db.commit()
			
			yield self.redirect("/Structure")
	Down.exposed = True
	
	# This function moves a given subforum up.  To do this, it swaps its order
	# with the subforum "above" it
	def Up(self, MoveUpID):
		if self.getLoginPermission() != 0:
			# Not an admin, error
			yield self.error('You do not have permission to view this page!', True)
		elif self.get_subforum_name(MoveUpID) == None:
			# The thread is not found, so error
			yield self.error('Specified subforum not found.')
		else:
			cursor = cherrypy.thread_data.db.cursor() 
			
			# Get this subforum's relative order
			cursor.execute("SELECT Subforum.RelativeOrder FROM Subforum WHERE Subforum.ID = ?", (MoveUpID,))
			
			MoveUpOrder = cursor.fetchone()[0]
			
			# Get the ID and Relative order of the subforum above this
			cursor.execute('''
				SELECT Subforum.ID, Subforum.RelativeOrder
				FROM Subforum
				WHERE Subforum.RelativeOrder IS
					(
					SELECT MAX(Subforum.RelativeOrder)
					FROM Subforum
					WHERE 
					Subforum.ParentSubforumID IS
						(
						SELECT Subforum.ParentSubforumID
						FROM Subforum
						WHERE Subforum.ID = ?
						)
					AND
					Subforum.RelativeOrder < 
						(
						SELECT Subforum.RelativeOrder
						FROM Subforum
						WHERE Subforum.ID = ?
						)
					)
					AND Subforum.ParentSubforumID IS
					(
					SELECT Subforum.ParentSubforumID
					FROM Subforum
					WHERE Subforum.ID = ?
					)
				''', (MoveUpID, MoveUpID, MoveUpID,))
			
			result = cursor.fetchone()
			
			if result != None:
				(MoveDownID, MoveDownOrder) = result
			
				# Swap the orders
				cursor.execute("UPDATE Subforum SET RelativeOrder = ? WHERE ID = ?", (MoveDownOrder,MoveUpID,))
				cherrypy.thread_data.db.commit()
				
				cursor.execute("UPDATE Subforum SET RelativeOrder = ? WHERE ID = ?", (MoveUpOrder,MoveDownID,))
				cherrypy.thread_data.db.commit()
			
			yield self.redirect("/Structure")
	Up.exposed = True
